package com.quanjiaolian.findins.serviceimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import com.quanjiaolian.DB.DataBase;
import com.quanjiaolian.findins.service.FindInsService;
import com.quanjiaolian.tools.LocationUtils;

public class FindInsServiceImpl implements FindInsService {
	

	/**
	 * 声明Log4j变量
	 */
	public static ResourceBundle resourceBundle = null;
	static Logger logger = Logger.getLogger(FindInsServiceImpl.class.getName());
	
	DataBase db = new DataBase();

	/**
	 * 声明数据库连接实例
	 */
	// 定义一个Connection 用来连接数据库
	Connection conn = null;
	// 声明PreparedStatement实例
	PreparedStatement pstmt = null;
	// 用于存放返回结果的数据集
	ResultSet rs = null;
	
	@SuppressWarnings("static-access")
	public List<Object> queryCountry(int city_id) {
		List<Object> list = new ArrayList<Object>();
		String sql = "SELECT bl.business_id,bl.county_name FROM business_location bl WHERE bl.parents_id = "+city_id;
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("county_id", rs.getInt(1));
				map.put("county_name", rs.getString(2));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return list;
	}
	@SuppressWarnings("static-access")
	public List<Object> queryBusiness(int country_id){
		List<Object> list = new ArrayList<Object>();
		String sql = "SELECT bl.business_name,bl.bn_latitude,bl.bn_longitud FROM business_location bl WHERE bl.parents_id = "+country_id;
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("business_name",rs.getString(1));
				map.put("bn_latitude",rs.getString(2));
				map.put("bn_longitud",rs.getString(3));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return list;
	}
	
	@SuppressWarnings("static-access")
	public List<Object> querySportType(){
		List<Object> list = new ArrayList<Object>();
		String sql = "SELECT st.st_id,st.st_name FROM sport_type st WHERE st.st_parents_id <> 0";
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("st_id",rs.getInt(1));
				map.put("st_name",rs.getString(2));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return list;
	}
	
	@SuppressWarnings("static-access")
	public List<Object> queryInsList(int firstSize, int maxSize,String sex, String teach_year, String pay_min,
			String pay_max, String audit_results, String latitude,
			String longitude, double minLat, double minLng, double maxLat,
			double maxLng,int type,String st_id) {
		List<Object> list = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ii.ins_id,ii.ins_name,ii.inspic_path,ii.ins_native_place,ii.teach_year,ii.ins_heart,ii.audit_results,il.ins_latitude,il.ins_longitud ");
		sql.append("FROM ins_info ii,ins_location il ");
		sql.append("WHERE ii.ins_id = il.ins_id ");
		if(sex != null){
			sql.append("AND ii.ins_sex = "+Integer.parseInt(sex)+" ");
		}
		if(st_id !=null ){
			sql.append("AND ii.st_id = "+Integer.parseInt(st_id)+" ");
		}
		if(teach_year !=null ){
			sql.append("AND ii.teach_year = "+Integer.parseInt(teach_year)+" ");
		}
		if(pay_min != null){
			sql.append("AND ii.teach_pay > "+pay_min+" ");
		} 
		if(pay_max != null){
			sql.append("AND ii.teach_pay < "+pay_max+" ");
		}
		if(audit_results != null){
			sql.append("AND ii.audit_results = "+Integer.parseInt(audit_results)+" ");
		}
		if(type == 1){
			sql.append("AND il.audit_results = 0 ");
			sql.append("AND il.ins_longitud > "+minLng+" ");
			sql.append("AND il.ins_longitud < "+maxLng+" ");
			sql.append("AND il.ins_latitude > "+minLat+" ");
			sql.append("AND il.ins_latitude < "+maxLat+" ");
			sql.append("ORDER BY ACOS(SIN(("+latitude+" * 3.1415) / 180 ) * SIN((il.ins_latitude * 3.1415) / 180 )");
			sql.append("+COS(("+latitude+" * 3.1415) / 180 ) * COS((il.ins_latitude * 3.1415) / 180 )");
			sql.append("*COS(("+longitude+" * 3.1415) / 180 - (il.ins_longitud * 3.1415) / 180 ) )");
			sql.append("* 6380 asc ");
		}else{
			sql.append("ORDER BY ii.record_date ");
		}
		sql.append("LIMIT "+(firstSize - 1) * maxSize+","+maxSize);
		logger.info("动态查询教练列表SQL语句："+sql.toString());
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("ins_id",rs.getInt(1));
				map.put("ins_name",rs.getString(2));
				map.put("inspic_path",rs.getString(3));
				map.put("ins_native_place",rs.getString(4));
				map.put("teach_year",rs.getInt(5));
				map.put("ins_heart",rs.getInt(6));
				map.put("audit_results",rs.getInt(7));
				map.put("distance", LocationUtils.distance(Double
						.valueOf(longitude.trim()), Double.valueOf(latitude
						.trim()), Double.valueOf(rs.getString(8).trim()),
						Double.valueOf(rs.getString(9).trim())));
//				map.put("ins_latitude",rs.getString(8));
//				map.put("ins_longitud",rs.getString(9));
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return list;
	}
	
	@SuppressWarnings("static-access")
	public List<Object> queryInsDetail(int ins_id){
		List<Object> list = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ii.ins_id,ii.ins_name,ii.teach_year,ii.inspic_path,ii.id_path,ii.ins_msisdn,ii.audit_results,ii.ins_description,ii.ins_honor,ii.teach_pay,ii.teach_way ");
		sql.append("FROM ins_info ii WHERE ii.ins_id = ?");
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1,ins_id);
			rs = pstmt.executeQuery();
			rs.next();
			Map<String,Object> map = new HashMap<String,Object>();
			map.put("ins_id",rs.getInt(1));
			map.put("ins_name",rs.getString(2));
			map.put("teach_year",rs.getInt(3));
			map.put("inspic_path",rs.getString(4));
			if(rs.getString(5) != null && !"".equals(rs.getString(5))){
				map.put("id_isbind","Y");
			}else{
				map.put("id_isbind","N");
			}
			if(rs.getString(6) != null && !"".equals(rs.getString(6))){
				map.put("msisdn_isbind","Y");
			}else{
				map.put("msisdn_isbind","N");
			}
			if(rs.getInt(7) == 0){
				map.put("audit_isbind","Y");
			}else{
				map.put("audit_isbind","N");
			}
			map.put("ins_description",rs.getString(8));
			map.put("ins_honor",rs.getString(9));
			map.put("teach_pay",rs.getString(10));
			map.put("teach_way",rs.getString(11));
			list.add(map);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}

		return list;
	}
	
//	public static void main(String[] args) {
//		FindInsServiceImpl fsi = new FindInsServiceImpl();
//	}

}
